cd ${work}5_reg_analysis

*** STATS BASED ON RAW EXPORT DATA
use jurnr vare land year vrd using input\export_raw_ipdt if land!="" , clear

destring vare, gen(cn8) force
drop if cn8==.
merge m:1 cn8 year using input\cn8_cn8plus_2002_2015, keepusing(cn8plus) keep(3) nogen
drop vare cn8
rename cn8plus vnr

collapse (sum) vrd , by(jurnr vnr land year)

save temp\export_raw_ipdt, replace


use temp\export_raw_ipdt, clear
collapse (sum) vrd_raw=vrd, by(jurnr year)
save temp\exp_vrd_raw, replace

use temp\export_raw_ipdt, clear
collapse (sum) vrd, by(jurnr land year)
collapse (count) dest_raw=vrd, by(jurnr year)
save temp\exp_dest_raw, replace

use temp\export_raw_ipdt, clear
collapse (sum) vrd, by(jurnr vnr year)
collapse (count) prod_raw=vrd, by(jurnr year)
save temp\exp_prod_raw, replace


*** STATS BASED ON CLEANED EXPORT DATA FOR SAMPLE FIRMS
use input\export_ipdt if vrd_exp_exsmall!=., clear
collapse (sum) vrd_sample=vrd_exp_exsmall, by(jurnr year)
save temp\exp_vrd_sample, replace

use input\export_ipdt if vrd_exp_exsmall!=., clear
collapse (sum) vrd_exp_exsmall, by(jurnr land year)
collapse (count) dest_sample=vrd_exp_exsmall, by(jurnr year)
save temp\exp_dest_sample, replace

use input\export_ipdt if vrd_exp_exsmall!=. , clear
collapse (sum) vrd_exp_exsmall, by(jurnr vnr year)
collapse (count) prod_sample=vrd_exp_exsmall, by(jurnr year)
save temp\exp_prod_sample, replace


*** MERGE AND CONSTRUCT TABLE
use jurnr year employees sales nace1 using input\firm_it if nace1=="C" & inrange(year,2002,2015) & employees>=.5 & employees!=., clear

merge 1:1 jurnr year using input\sample_it, assert(1 3) keepusing(jurnr)
gen sample=(_m==3)
drop _m

foreach x in raw sample {
	foreach y in vrd dest prod {
		merge 1:1 jurnr year using temp\exp_`y'_`x', keep(1 3) keepusing(`y'_`x') nogen
		erase temp\exp_`y'_`x'.dta
		replace `y'_`x'=0 if `y'_`x'==.
	}
}
gen exp_raw=(vrd_raw>0)
gen exp_sample=(vrd_sample>0)


matrix mat = J(3+4,2,.)

matrix mat[1,1] = 100
matrix mat[2,1] = 100
matrix mat[3,1] = 100
local n=4
foreach v of varlist employees exp_raw prod_raw dest_raw {
	qui sum `v'
	matrix mat[`n',1] = r(mean)
	local ++n
}

qui count
local N=r(N)
qui count if sample==1
matrix mat[1,2]=r(N)/`N' *100

qui sum sales
local sum=r(sum)
qui sum sales if sample==1
matrix mat[2,2]=r(sum)/`sum' *100

qui sum vrd_raw
local sum=r(sum)
qui sum vrd_sample if sample==1
matrix mat[3,2]=r(sum)/`sum' *100

local n=4
foreach v of varlist employees exp_sample prod_sample dest_sample {
	qui sum `v' if sample==1
	matrix mat[`n',2] = r(mean)
	local ++n
}

matrix colnames mat = "All Manufacturing Firms" "Estimation Sample"
matrix rownames mat = "Observations" "Total Sales" "Export Sales" "Employees" "Export Status" "Exported Products" "Export Destinations"
esttab matrix(mat, fmt(2)) using output\table_a1, nomtitles booktabs replace